0% completed
In relational databases, keys play a crucial role in uniquely identifying records within a table, establishing relationships between tables, and maintaining data integrity. Keys ensure that each record in a database can be accurately retrieved, updated, or deleted without ambiguity. This lesson covers the different types of keys used in relational databases.
Types of Keys
Primary Key
A primary key is a unique identifier for each record in a table. It ensures that each row in a table is distinct, meaning no duplicate values are allowed in the primary key column(s). Primary keys cannot contain null values.
- Example: In a Student table, Roll Number could be a primary key since each student has a unique roll number.
Candidate Key
A candidate key is any attribute, or a combination of attributes, that can uniquely identify a row in a table. A table can have multiple candidate keys, but only one of them can be chosen as the primary key.
- Example: In the Student table, both Roll Number and Email (if each student has a unique email) could be candidate keys, as both can uniquely identify a student.
Alternate Key
An alternate key is a candidate key that is not selected as the primary key. It serves as an alternative way to uniquely identify records within the table.
- Example: If Roll Number is chosen as the primary key, Email would be an alternate key for the Student table.
Foreign Key
A foreign key is an attribute in one table that links to the primary key of another table. Foreign keys establish relationships between tables, enforcing referential integrity by ensuring that values in the foreign key column must match values in the referenced primary key column.
- Example: In an Student table that records the student data, CourseID, which is primary key in the Course table, could be a foreign key in the Student table.
Composite Key
A composite key is a key that consists of two or more attributes used together to uniquely identify a record. Composite keys are often used when no single attribute can uniquely identify a row.
- Example: In an Enrollment table, a combination of Student ID and Course ID might serve as a composite key, uniquely identifying each enrollment record.
Super Key
A super key is any combination of attributes that can uniquely identify a row in a table. Super keys include all candidate keys, primary keys, and any additional attributes that make them unique. Super keys may contain more attributes than necessary to ensure uniqueness.
- Example: In the Student table, {Roll Number, Name} and {Roll Number, Email} are super keys because they uniquely identify each row, although Roll Number alone could also act as a key.
Summary Table of Keys
| Key Type | Purpose | Example |
|---|---|---|
| Primary Key | Uniquely identifies each record in a table | Roll Number in Student |
| Candidate Key | Potential attributes for unique identification | Roll Number, Email |
| Alternate Key | Candidate key not chosen as primary key | Email if Roll Number is primary |
| Foreign Key | Establishes a link between two tables | Course ID in Student |
| Composite Key | Combines multiple attributes to ensure uniqueness | Student ID and Course ID |
| Super Key | Any set of attributes that uniquely identifies a row | {Roll Number, Name} |
On this page
Types of Keys
Primary Key
Candidate Key
Alternate Key
Foreign Key
Composite Key
Super Key
Summary Table of Keys